<?php
namespace App\Http\Query;

use Illuminate\Support\Facades\DB;
use Log;

/*
 *設定マスタ
 */
class SettingQuery
{
    // 設定マスタ データを取る
    public function getSettingMasterInfo()
    {
        $sql = "select sm.created_at,
                MAX(IF(sm.comment = '税率設定',sm.set_value1,'')) 'comment1',
                MAX(IF(sm.comment = '送料設定',sm.set_value1,'')) 'comment2',
                MAX(IF(sm.comment = '送料無料設定',sm.set_value1,'')) 'comment3',
                MAX(IF(sm.comment = '当日出荷時刻',sm.set_value1,'')) 'comment4',
                MAX(IF(sm.comment = '在庫以上',sm.set_value1,'')) 'comment5',
                MAX(IF(sm.comment = '在庫期間',concat(sm.set_value1,' ～ ',sm.set_value2),'')) 'comment6',
                MAX(IF(sm.comment = '在庫以下',sm.set_value1,'')) 'comment7'
                from setting_master sm where sm.delete_flg = 0 group by sm.created_at";

        return  DB::select($sql);
    }

    // データを保存 setting_master
    public function saveSettingMaster($SettingMasterInfo)
    {

        for ($i = 0; $i < 7; $i ++) {
            $sql = 'INSERT INTO setting_master(set_value1, set_value2, comment, delete_flg, created_at, updated_at) VALUES (?,?,?,0,now(),now())';
            $param = $SettingMasterInfo[$i];
            $flag = DB::insert($sql,$param);
        }
         return $flag;
    }

    // データを削除 setting_master
    public function deleteSettingMaster($createdAt)
    {

        $sql = 'UPDATE setting_master SET delete_flg = 1 WHERE  created_at = ?';

        $param = [$createdAt];

        $delflag = DB::update($sql,  $param);
        return $delflag;
    }

    // データを修正 setting_master
    public function selectSettingMasterBycreatedAt()
    {

        //$sql = 'select * from setting_master  where  created_at = ?  and delete_flg = 0';
        $sql = "select sm.created_at,
                MAX(IF(sm.comment = '税率設定',sm.set_value1,'')) 'comment1',
                MAX(IF(sm.comment = '送料設定',sm.set_value1,'')) 'comment2',
                MAX(IF(sm.comment = '送料無料設定',sm.set_value1,'')) 'comment3',
                MAX(IF(sm.comment = '当日出荷時刻',sm.set_value1,'')) 'comment4',
                MAX(IF(sm.comment = '在庫以上',sm.set_value1,'')) 'comment5',
                MAX(IF(sm.comment = '在庫期間',sm.set_value1,'')) 'comment6',
                MAX(IF(sm.comment = '在庫期間',sm.set_value2,'')) 'comment7',
                MAX(IF(sm.comment = '在庫以下',sm.set_value1,'')) 'comment8'
                from setting_master sm where sm.delete_flg = 0 group by sm.created_at";


        return  DB::select($sql);
    }

    // データを修正 setting_master
    public function updateSettingMaster($SettingMasterInfo)
    {

        for ($i = 0; $i < 7; $i ++) {

            $sql = 'UPDATE setting_master SET set_value1 = ?,set_value2 = ? WHERE  delete_flg = 0 AND comment = ?  AND created_at = ?';

            $param = $SettingMasterInfo[$i];

            $flag = DB::update($sql,$param);
        }
        return $flag;
    }


}